clear
import excel "E:\Dropbox\Search\Submissions\RCFS\Final\PseudoData.xlsx", sheet("data") firstrow clear

*** Construct variables ***
gen under = (prc_post/prc_offr) - 1
gen discount = 1 - (prc_offr/prc_pre)
gen duration = ((prc_date_news-ann_date_news)*24) + ((prc_time_news-ann_time_news)/3600000)
gen mcap18 = prc_pre*shrout/defl18
gen proc18 = prc_offr*shr_offr/defl18
gen offr_frac = proc18/mcap18
gen eqt = ceq
	replace eqt=seq if missing(eqt)
gen mtob = (at - eqt + (prcc_f*csho))/at
gen rd_to_at = rd/at
	replace rd_to_at = 0 if missing(rd)
gen lev = lt/at
gen cash_to_at = che/at
gen roa = ni/at
gen gics2 = int(gics/10000)
gen year = year(ann_date_news)
gen cm_rank_id = cm_rank*10

*** Winsorize variables ***
winsor2 exret_ann under discount duration mcap18 proc18 offr_frac sigma_ret turn bidask mtob rd_to_at lev cash_to_at roa exret_cum90 instown numest cm_rank , replace cuts(5 95)

*** Adjust units of measurement for summary table ***
gen exret_ann_pct = exret_ann*100
gen under_pct = under*100
gen discount_pct = discount*100
gen mcap18mill = mcap18/1000000
gen proc18mill = proc18/1000000
gen offr_frac_pct = offr_frac*100 
gen sigma_ret_pct = sigma_ret*100
gen turn_pct = turn*100
gen bidask_pct = bidask*100
gen rd_to_at_pct = rd_to_at*100
gen lev_pct = lev*100
gen cash_to_at_pct = cash_to_at*100
gen roa_pct = roa*100
gen exret_cum90_pct = exret_cum90*100
gen instown_pct = instown*100

*** Create log variables for regression models ***
gen ln_mcap18 = ln(mcap18)
gen ln_turn = ln(turn)
gen ln_mtob = ln(mtob)
gen ln_numest = ln(1+numest)

*** Table 1: Summary statistics ***
tabstat exret_ann_pct under_pct discount_pct duration cmpo_dum mcap18mill proc18mill offr_frac_pct sigma_ret_pct turn_pct bidask_pct mtob rd_to_at_pct lev_pct cash_to_at_pct roa_pct exret_cum90_pct instown_pct numest cm_rank , s(n mean sd p25 p50 p75) format(%12.2f) col(stats) long save
*return list
*putexcel set tabstat.xlsx, replace
*putexcel A1 = matrix(r(StatTotal)), names

*** Table 2: Models to predict pricing speed and CMPO ***
preserve
eststo clear
eststo: reg   duration cmpo_dum ln_mcap18 offr_frac sigma_ret ln_turn bidask ln_mtob rd_to_at lev cash_to_at roa exret_cum90 instown ln_numest ib90.cm_rank_id ib2018.year ib35.gics2, cluster(permco)
eststo: logit          cmpo_dum ln_mcap18 offr_frac sigma_ret ln_turn bidask ln_mtob rd_to_at lev cash_to_at roa exret_cum90 instown ln_numest ib90.cm_rank_id ib2018.year ib35.gics2, cluster(permco)
margins, dydx(*)
esttab, b(3) t(3) pr2(4) ar2(4) nogaps star(* 0.10 ** 0.05 *** 0.01) replace
*esttab * using "C:\Table2.csv", b(3) t(2) pr2(4) ar2(4) nogaps star(* 0.10 ** 0.05 *** 0.01) replace
restore

*** Table 3: Models examing the relation between duration and SEO outcomes 
preserve
eststo clear
eststo: reg exret_ann_pct   duration cmpo_dum, vce(cluster permco)
eststo: reg exret_ann_pct   duration cmpo_dum ln_mcap18 offr_frac sigma_ret ln_turn bidask ln_mtob rd_to_at lev cash_to_at roa exret_cum90 instown ln_numest ib90.cm_rank_id ib2018.year ib35.gics2, vce(cluster permco)
eststo: reg under_pct       duration cmpo_dum, vce(cluster permco)
eststo: reg under_pct       duration cmpo_dum ln_mcap18 offr_frac sigma_ret ln_turn bidask ln_mtob rd_to_at lev cash_to_at roa exret_cum90 instown ln_numest ib90.cm_rank_id ib2018.year ib35.gics2, vce(cluster permco)
eststo: reg discount_pct    duration cmpo_dum, vce(cluster permco)
eststo: reg discount_pct    duration cmpo_dum ln_mcap18 offr_frac sigma_ret ln_turn bidask ln_mtob rd_to_at lev cash_to_at roa exret_cum90 instown ln_numest ib90.cm_rank_id ib2018.year ib35.gics2, vce(cluster permco)
esttab , b(3) t(2) ar2(4) nogaps star(* 0.10 ** 0.05 *** 0.01)
*esttab * using "C:\Table3.csv", b(3) t(2) ar2(4) nogaps star(* 0.10 ** 0.05 *** 0.01) replace
restore
